#!/user/bin/env python
# -*-coding:utf-8-*-
'''
@author: Patty
@project: automation_framework
@file: File.py
@time: 2019/10/29  17:58
@IDE: PyCharm 
'''

import logging, time, requests, traceback, functools
import base.globalvars as glo
from proj_spec.DAS.model import get_model_id,get_model_coverage
from proj_spec.DAS.element import get_element_id
from proj_spec.DAS.workflow import STEP_FIELDS_MAP,ONE_CLICK_EXTRACT_STEPS
from proj_spec.DAS.basedata import get_entity_info
from util.date_util import timestamp_to_str
from util.db_util import execute_query,get_query_result
from util.jsonmatch import my_list_cmp
from util.collection_util import random_select_from_list

def __init():
    """"""
    from base.get_config import get_header_file
    from base.getdata import get_header
    global url1, header1, header2
    url1 = glo.get_value('url1')
    header1 = get_header(get_header_file(), "header1")
    header2 = get_header(get_header_file(), "header2")


def get_file_id(name):
    """获取指定文件名称的id,相同名称的文件，去vfile_id最大的一个（最新）

    :param name: 文件名称

    :return: 文件id
    """
    results = execute_query("SELECT f.id FROM `t_das_file` f inner join t_das_std_vfile v on v.file_id = f.id "
                            " WHERE `title`='%s' order by v.id desc" % name)
    try:
        result = results[0][0]
        return result
    except Exception as e:
        logging.error("no result retrieved")
        return None


def get_file_tags_str(file_id):
    """获取文件标签字符串

    :param file_id: 文件id

    :return: 以逗号分隔的文件标签字符串
    """
    sql = "SELECT tag_ids from t_das_file where id=%s" % file_id
    res = execute_query(sql)
    try:
        tag_ids_str = res[0][0]
        return tag_ids_str
    except Exception as e:
        return ""


def get_file_tags(file_id):
    """获取文件标签id的列表

    :param file_id: 文件id

    :return: 文件标签id的列表
    """
    tag_ids = get_file_tags_str(file_id)
    if tag_ids != "":
        return tag_ids.split(',')
    else:
        return []


def get_file_info(file_id):
    """获取文件基本信息

    :param file_id: 文件id

    :return: 文件基本信息的字典
    """
    # sql = """select report_year,report_period,category_id,type_id,publish_date,title,org_id,tag_ids,is_delete
    #         from t_das_file where id=%s"""%file_id
    sql = """select * from t_das_file where id=%s""" % file_id
    try:
        res = execute_query(sql, result_type="dict")
        # data = pd.Series(res[0],index=["report_year","report_period","category_id","type_id","publish_date","title","org_id","tag_ids","is_delete"])
        # return data
        return res[0]
    except Exception as e:
        logging.error("get_file_info:%s" % e)
        traceback.print_exc()
        # return pd.Series([])
        return None


def get_file_params_for_template_mapping_crieria(file_id):
    """获取查询文件匹配的映射关系所需的各项参数

    :param file_id: 文件id

    :return: 指定文件report_year,report_period,category_id,type_id,tag_ids
    """

    sql = "select report_year,report_period,category_id,type_id,tag_ids from t_das_file where id=%s" % file_id
    res = execute_query(sql)
    return res[0]


def get_file_entity_code(file_id):
    """获取文件所属的entity_code

    :param file_id: 文件id

    :return: 指定文件的entity_code
    """

    # sql = "select entity_code from t_das_file where id=%s"%file_id
    sql = "select e.entity_code from t_das_entity_info e inner join t_das_file f on e.org_id=f.org_id where f.id=%s" % file_id
    try:
        res = execute_query(sql)
        return res[0][0]
    except Exception as e:
        return ""


def get_file_sector(file_id):
    """获取文件所属公司的行业

    :param file_id: 文件id

    :return: 指定文件所在公司所属的行业
    """

    # sql = "select sector from t_das_file where id=%s"%file_id
    sql = "select e.sector from t_das_entity_info e inner join t_das_file f on e.org_id=f.org_id where f.id=%s" % file_id
    try:
        res = execute_query(sql)
        return res[0][0]
    except Exception as e:
        return ""


def get_file_matched_published_models(file_id):
    """获取和文件匹配的已发布模型id列表

    :param file_id: 文件id

    :return: 和文件匹配的已发布模型id的列表
    """

    # 2020-05-07: 匹配条件增加市场行业，文件无标签时若市场行业匹配仍可匹配模型

    file_info = get_file_info(file_id)
    category_id = file_info['category_id']
    type_id = file_info['type_id']
    org_id = file_info['org_id']
    tag_ids = file_info['tag_ids'].split(',')
    entity_info = get_entity_info(org_id)
    market = entity_info['market']
    sector = entity_info['sector']

    if tag_ids == []:  # 文件无标签
        sql = """select a.id from t_das_model a
                join t_das_model_type mt
                on mt.model_id=a.id
                where mt.category_id=%s and mt.type_id = %s and a.status=4 and active_status =0 and (a.tag_ids='' or a.tag_ids is null)
                and (a.markets like '%s' or a.markets is null or a.markets='')
                 and a.sectors like '%s' or a.sectors is null or a.sectors=''""" \
              % (category_id, type_id, '%%' + market + '%%', '%%' + sector + '%%')
        # Todo:市场行业现有值可以用like匹配，若将来存在如CORPXXX的值改进？

    else:  # 文件有标签，则标签须在模型标签列表内，或模型无标签 测试：文件标签1,4 模型标签 1,2,4
        sql = """select distinct c.id from (select a.id,substring_index(substring_index(a.tag_ids,',',b.id),',',-1) 
                            as 'tag_id' from t_das_model a
                            join t_das_type b 
                            on b.id <= (length(a.tag_ids) - length(replace(a.tag_ids,',',''))+1)
                            join t_das_model_type mt
                            on mt.model_id=a.id
                            where mt.category_id=%s and mt.type_id = %s and a.status=4 and active_status =0 
                            and (a.markets like '%s' or a.markets is null or a.markets='') 
                            and (a.sectors like '%s' or a.sectors is null or a.markets='')
                            ) as c
                            where c.tag_id in (%s) or c.tag_id is null or c.tag_id=''""" % (
            category_id, type_id, '%%' + market + '%%', '%%' + sector + '%%',
            ','.join(['%s'] * len(tag_ids)))  # 使用'%%%s%%'写法会报not enough arguments for format string
        # category_id, type_id, '%%'+market+'%%','%%'+sector+'%%',"','".join([str(id) for id in tag_ids])) #使用'%%%s%%'写法会报not enough arguments for format string
        # category_id, type_id, '%%' + market + '%%', '%%' + sector + '%%', "'" + "','".join([str(id) for id in tag_ids]) + "'")  # 使用'%%%s%%'写法会报not enough arguments for format string

    res = execute_query(sql, tag_ids)
    try:
        ids = [row[0] for row in res]
        file_fyp = get_file_fyp(file_id)
        # 查看模型是否设置了财年财季适用范围
        for id in ids:
            matched = False
            coverage_conf_list = get_model_coverage(id)
            if len(coverage_conf_list)==0: #模型没有设置适用范围
                matched = True
            else:
                for config in coverage_conf_list:
                    if file_fyp>=str(config['start_fyp']) and file_fyp<=str(config['end_fyp']): # 只要文件的财年财季适配至少个配置项，则文件匹配该模型
                        matched= True
            if matched is False:
                ids.remove(id)

        return ids
    except Exception as e:
        logging.error("未获取到匹配的模型:category_id %s,type_id %s,tag_ids %s\n%s" % (category_id, type_id, tag_ids, e))
        return []


def process_file_and_wait_for_finish(file_id, start_time, wait_steps=None, restate=False):
    """执行文件一键提取或重述计算并等待完成

    :param file_id: 文件id
    :param start_time: 过程开始时间，time.time()
    :param wait_steps: 过程开始后等待执行的步骤列表，用于预估耗时
        | 0：提取 1：结构化 2：结构化质检 3：标准化 4：标准化质检 5：模板计算 6：模板质检
    :param restate: 是否为执行重述计算

    :return: 无

    """
    __init()

    if wait_steps is None:
        wait_steps = ONE_CLICK_EXTRACT_STEPS


    if restate is True:
        logging.info("start time: %s" % timestamp_to_str(start_time))
        logging.info("重述计算文件：%s" % file_id)
        url = url1 + "/api/v1/File/start-restatement-process/%s" % file_id
    else:
        logging.info("start time: %s" % timestamp_to_str(start_time))
        logging.info("一键提取文件：%s" % file_id)
        # 一键提取
        url = url1 + "/api/v1/File/start-process/%s" % file_id
    res = requests.post(url, headers=header1)
    assert res.status_code == 200, "文件提取状态码%s,%s" % (res.status_code, res.text)

    if restate is True:
        assert res.text=='','重述计算时不应匹配模型：%s'%res.text
        runs = 1
    else:
        # 验证接口返回的模型id与文件匹配的模型一致
        result_models = res.text.lstrip('[').rstrip(']').split(',')
        result_models = [int(id) for id in result_models]
        matched_models = get_file_matched_published_models(file_id)
        runs = len(matched_models)
        assert my_list_cmp(matched_models, result_models)

    # 仅基准模型验证执行结果
    costs = [STEP_FIELDS_MAP[step]['cost'] for step in wait_steps]  # 每个文件预估执行提取过程各步骤的耗时
    sum_costs = sum(costs)
    # 轮询直到达到超时时间
    while time.time() - start_time < runs * sum_costs:  # 每个文件处理时间，超过则超时
        time.sleep(5)

    time.sleep(5)


def get_file_fyfq(file_id):
    """获取拼接的财年财季字符串

    :param file_id: 文件id

    :return: 文件所在财年财季字符串，如文件所在财年2018，财季12，则返回201812
    """

    sql = "select report_year,report_period from t_das_file where id=%s" % file_id
    res = execute_query(sql)

    try:
        return str(res[0][0]) + str(res[0][1])
    except Exception as e:
        logging.error(e)
        return ""


def get_vfile_id(file_id):
    """获取文件vfile_id

    :param file_id: 文件id

    :return: 文件vfile_id
    """
    sql = "select id from t_das_std_vfile where file_id=%s order by id desc" % file_id
    try:
        res = execute_query(sql)
        return res[0][0]
    except Exception as e:
        logging.error(e)
        traceback.print_exc()
        return -1

def get_file_id_from_vfile(vfile_id):
    """根据vfile_id获取file_id

    :param vfile_id:

    :return: 文件id
    """
    sql = "select file_id from t_das_std_vfile where id=%s" % vfile_id
    try:
        res = execute_query(sql)
        return res[0][0]
    except Exception as e:
        logging.error(e)
        traceback.print_exc()
        return -1

def get_restate_files(restate_type, files):
    """随机获取符合重述规则的文件对, 在指定文件列表内查找

    :param restate_type: 重述类型：1-期末，2-同期
    :param files: 文件列表(实为和指定模型匹配的文件列表)

    :return: 上期文件,本期文件的元组
    """

    file_sub_str = " WHERE id IN (%s)"%(','.join(['%s'] * len(files)))

    # MySQL 5.x实现窗口函数：https://www.cnblogs.com/gered/p/10430829.html
    # 文件按org_id分组后在分组内按报告期倒序排列
    sub_sql = """SELECT
                    @num := IF(@org_id = org_id, @num + 1, 1) num,
                    id,
                    report_year,
                    report_period,
                    @org_id:=org_id org_id # 将org_id更新为当前记录的值
            FROM
                (SELECT id,org_id,report_year,report_period
                #CONCAT(report_year,RIGHT(concat('00', SUBSTRING(CONCAT(report_year, report_period), 5, 2)), 2)) as ryrp
                FROM t_das_file %s) AS f,
                (SELECT @org_id := '', @num :=0) t1 # 设置org_id初始值 
            ORDER BY
                org_id, report_year, report_period DESC"""%file_sub_str

    if restate_type==1: #期末
        sql = """SELECT id,org_id,report_year,report_period
                FROM
                (
                    SELECT 
                        #@count := IF(@org_id = org_id AND @report_period=12 AND report_year=@report_year+1, @count+1, 1) count,
                        @count := IF(report_period=12,1,IF(@org_id = org_id AND @report_period=12 AND report_year=@report_year+1, @count+1, 0)) count,
                        id,
                        @report_year := report_year report_year,
                        @report_period := report_period report_period,
                        @org_id:=org_id org_id
                    FROM
                        (
                        %s) as grouped1,
                        (SELECT @org_id :='', @count :=0, @report_year:='', @report_period:='' ) t2
                    ORDER BY org_id, report_year, report_period
                ) grouped2
                WHERE count>1 # 本期文件，如果找count=1的上期文件，不一定有对应的本期文件""" % sub_sql

        res = execute_query(sql, files)

        if len(res)>0:

            current_period_file_id, org_id ,report_year, report_period= random_select_from_list(res,1)[0] #随机选取一个本期文件

            sql2 = """SELECT id FROM t_das_file %s AND org_id='%s' AND 
                        report_year=%s-1 AND report_period=12 #下年半年报或年报重述上年年末数据
                        ORDER BY report_year,report_period
                """%(file_sub_str, org_id, report_year)

            files = [row[0] for row in execute_query(sql2,files)] #下年半年报或下年年报
            if len(files)!=0:
                last_period_file_id = random_select_from_list(files,1)[0]
                return last_period_file_id,current_period_file_id
            else:
                logging.error("未找到下年文件")
                return ()
        else:
            logging.error("未找到符合重述类型期末的文件")
            return ()


    elif restate_type==2: #同期
        sql = """SELECT id,org_id,report_year,report_period
                FROM(
                    SELECT
                        @count := IF(@org_id=org_id AND @report_period=report_period AND @report_year<>report_year, @count + 1, 1) count,
                        id,
                        # 将org_id,report_year,report_period更新为当前记录的值
                        @org_id:=org_id org_id,
                        @report_year:=report_year report_year,
                        @report_period:=report_period report_period
                    FROM
                        (%s) as grouped,
                        # 设置org_id,report_year,report_period的初始值
                        (SELECT @org_id := '', @report_year :='', @report_period:='', @count :=0) t2
                    ORDER BY org_id, report_period, report_year) grouped2
                WHERE count>1 #同期中的本期文件（有相同report_period的上期文件）""" % (sub_sql)

        res = execute_query(sql, files)

        if len(res)>0:

            current_period_file_id, org_id, report_year,report_period = random_select_from_list(res, 1)[0]  # 随机选取一个上期文件

            sql2 = """SELECT id FROM t_das_file %s AND org_id='%s' AND 
                                report_year=%s-1 AND report_period =%s #上年同期报告
                                ORDER BY report_year,report_period
                        """ % (file_sub_str, org_id, report_year,report_period)

            files = [row[0] for row in execute_query(sql2,files)]  # 上年同期报告
            if len(files) != 0:
                last_period_file_id = random_select_from_list(files, 1)[0]
                return last_period_file_id, current_period_file_id
            else:
                logging.error("未找到上年同期文件")
                return ()
        else:
            logging.error("未找到符合重述类型同期的文件")
            return ()
    else:
        logging.info("重述规则应为1或2")
        return ()


def get_preprocess_time(name):
    """获取二次预处理的时间
    :param : 文件名称

    :return: 返回二次预处理的时间
    """

    file = get_file_id(name)
    sql = "select manual_preprocess_time from t_das_file_preprocess where file_id = '%s'"%file
    try:
        res = execute_query(sql)
        return res[0][0]
    except Exception as e:
        logging.error(e)


def get_preprocess_id(name):
    """获取预处理的ID
    :param : 文件名称

    :return: 返回预处理的ID
    """

    file = get_file_id(name)
    sql = "select id from t_das_file_preprocess where file_id = '%s'" %file
    try:
        res = execute_query(sql)
        return res[0][0]
    except Exception as e:
        logging.error(e)

@functools.lru_cache(maxsize=128)
def get_file_restate_setting(file_id):
    """获取文件的使用重述值设置（是否使用重述值进行模板计算和模板质检）

    :param file_id: 文件id

    :return: True/False
    """
    sql = "SELECT restate_usage FROM t_das_restate_dashboard WHERE file_id=%s"%file_id
    #flag = get_query_result(sql, sys._getframe().f_code.co_name)
    result = get_query_result(sql)
    if result is not None and result!={}:
        flag = result['restate_usage']
    else:
        flag = 0

    return True if flag==1 else False


def get_fail_request_id(file_name, model_name, element_name):
    """获取OCR错误的请求ID
    :param file_name: 文件名称
    :param model_name: 模型名称
    :param element_name: 元素名称

    :return: 返回OCR错误的请求ID
    """

    file_id = get_file_id(file_name)
    model_id = get_model_id(model_name)
    element_id = get_element_id(model_name, element_name)
    sql = "select request_id from t_das_ocr_fail_reply where file_id = %s and model_id = %s and element_id = %s " % (file_id, model_id, element_id)
    try:
        res = execute_query(sql)
        return res[0][0]
    except Exception as e:
        logging.error(e)


def get_fail_request_body(request_id):
    """获取OCR错误的请求body
    :param request_id: 请求ID

    :return: 返回OCR错误的请求body
    """

    sql = "select request_body from t_das_ocr_request where request_id ='%s ' " % request_id
    try:
        res = execute_query(sql)
        return res[0][0]
    except Exception as e:
        logging.error(e)

def execute_one_click_extract(file_id):
    """对文件执行一键提取

    :param file_id: 文件id

    :return:
    """
    __init()
    file_process_url = url1 + '/api/v1/File/start-process/%s' % file_id
    res = requests.post(file_process_url, headers=header2)
    assert res.status_code == 200, "文件一键提取失败：%s" % res.text

def execute_restate_calc(file_id):
    """对文件执行重述计算

    :param file_id: 文件id

    :return:
    """
    __init()
    file_process_url = url1 + '/api/v1/File/start-restatement-process/%s' % file_id
    res = requests.post(file_process_url, headers=header2)
    assert res.status_code == 200, "文件重述计算失败：%s" % res.text


def get_file_fyp(file_id):
    """获取文件的完整报告期(形如201206)，report_period为3,6的填充0

    :param file_id: 文件id

    :return:  文件的完整报告期(形如201206)，report_period为3,6的填充0
    """

    sql = "select report_year,report_period from t_das_file where id=%s"%file_id
    res = execute_query(sql, result_type='dict')

    assert len(res)==1, "查询文件报告期出错"

    report_year = res[0]['report_year']
    report_period = res[0]['report_period']

    if int(report_period) == 12:
        complete_report_period = str(report_year) + str(report_period)
    else:
        complete_report_period = str(report_year) + '0' + str(report_period)

    return complete_report_period


def validate_file_ext(file_id):
    """验证文件是否包含扩展字段（证券信息）

    :param file_id: 文件名称

    :return:True/False
    """
    sql = "SELECT * FROM `das`.`t_das_file_ext` WHERE `file_id` = '%s'" % file_id
    results = execute_query(sql, result_type='dict')
    try:
        secu = results[0]['secu']
        ticker = results[0]['ticker']
        market_code = results[0]['market_code']
        stock_id = results[0]['stock_id']
        return all([secu, ticker, market_code, stock_id])
    except Exception as e:
        logging.error("no result retrieved:%s" % e)
        return None


def get_file_ext(file_id, *args):
    """获取文件扩展字段值（证券信息）

    :param file_id: 文件名称
    :param *args: 需获取的证券信息字段

    :return:文件证券信息内容
    """
    sql = "SELECT %s FROM `das`.`t_das_file_ext` WHERE `file_id` = '%s'" % (*args, file_id)
    results = execute_query(sql)
    try:
        return results[0][0]
    except Exception as e:
        logging.error("no result retrieved:%s" % e)
        return None
